#!/usr/bin/python3

proc = """ DELIMITER //
CREATE PROCEDURE InsAlbum_wFkey(
                          OUT artistID INT(11),
                          IN rotation INT(11),
                          IN genre INT(11),
                          IN description TEXT,
                          IN releasedate DATETIME,
                          IN receivedate DATETIME,
                          IN goforads DATETIME,
                          IN promoter VARCHAR(1000),
                          IN albumname VARCHAR(255),
                          IN artistName VARCHAR(255)
                         )
BEGIN
        INSERT INTO Album(ArtistID, Rotation, Genre, Description, ReleaseDate, ReceiveDate, GoForAds, Promoter, AlbumName)
                VALUES (artistID, rotation, genre, description, releasedate, receivedate, goforads, promoter, albuname);
        SELECT DISTINCT Artist.artistId FROM Artist WHERE artistName = Artist.artistName INTO artistID; 
        END //
DELIMITER ;
        """

data = open("flat_file.csv", "r")
output = open("parsedtables/populate_album.sql", "w")
err = open("parsedtables/album_err.txt", "w")

prefix = "InsAlbum_wFkey ("
suffix = ");\n"
albumName = 2
rotation =  "NULL"
genre =  5
description =  "NULL"
releaseDate = "NULL"
receiveDate = 3
goForAds =  4
promoter =0
delim = ","

output.write(proc + "\n")

for line in data:
  fields = line.split(delim)
  if(len(fields) >= 10):
    output.write(
        prefix + 
        rotation + delim + 
        str(fields[genre]) + delim + 
        description + delim + 
        releaseDate + delim +
        str(fields[receiveDate]) + delim +
        str(fields[goForAds]) + delim +
        str(fields[promoter]) + delim +
        str(fields[albumName]) + 
        suffix)
  else: 
    err.write(line)

data.close()
output.close()
err.close()
